﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
//添加引用
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.OleDb;
using System.Reflection;

namespace Mmht.DB
{
    public class UploadExcelDB
    {
        operate op = new operate();
        TableTerminologyDB tt = new TableTerminologyDB();
        TableStructurePropertyDB tsp = new TableStructurePropertyDB();
        TableHeatTreatingDataDB th = new TableHeatTreatingDataDB();
        TableSteelCompositionDB tsc = new TableSteelCompositionDB();
        TableMaterialsNumberDB tm = new TableMaterialsNumberDB();

        /// <summary>
        /// 获得文件中存在的全部工作表名
        /// </summary>
        /// <param name="Path">excel文档路径</param>
        /// <returns>excel文档中工作表明列表</returns>
        public DataTable GetSheetsName(string filepath)
        {
            DataTable dt = new DataTable();
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filepath + ";" + "Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            try
            {
                //   Get   the   data   table   containg   the   schema   guid.   
                dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                for (int i = 0; i < dt.Rows.Count;i++ )
                {
                    string sheetname = dt.Rows[i]["TABLE_NAME"].ToString();
                    dt.Rows[i]["TABLE_NAME"] = sheetname.Substring(0, sheetname.Length - 1);
                }
                return dt;
            }
            catch
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
        }

        

        /// <summary>
        /// 将欲导入Excel数据转换为dataset格式
        /// </summary>
        /// <param name="Path"></param>
        /// <param name="SheetName"></param>
        /// <returns></returns>
        public DataTable ExcelToDataTable(string Path, String SheetName)
        {
            DataTable dt = new DataTable();
            if (Path == null || SheetName == null)
            {
                return null;
            }
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
            DataSet ds = new DataSet();
            OleDbConnection conn = new OleDbConnection(strConn);
            //选取某个工作簿里的数据信息
            string strExcel = "select * from [" + SheetName + "$]";
            OleDbDataAdapter myCommand = new OleDbDataAdapter();
            myCommand = new OleDbDataAdapter(strExcel, strConn);

            try
            {
                //将数据信息填充入dataset
                myCommand.Fill(ds, "table1");
                dt = ds.Tables["table1"];
            }
            catch 
            {
                return null;
            }
            return dt;

        }

        public string UpdateTable(string sheetname, DataTable dt,int create_by)
        {
            string message = string.Empty;
            switch (sheetname)
            {
                case "牌号及其化学成分":
                    message = tsc.UpdateSteelComposition(dt, create_by);
                    break;
                case "材料编号":
                    message = tm.UpdateMaterialsNumber(dt, create_by);
                    break;
                case "组织和性能": 
                    message = tsp.UpdateStructureProperty(dt, create_by);
                    break;
                case "热处理数据":
                    message = th.UpdateHeatTreatingData(dt, create_by);
                    break;
                case "名词术语": 
                    message = tt.UpdateTerminology(dt,create_by);
                    break;
                default: break;
            }
            return message;
        }
    }
}
